Incorporating parameter fields into record selection formulas

Instead of displaying all of a report's data every time the report is opened, you can create parameter fields that prompt users to specify the data they want to see. To decrease the amount of data transferred from the database server, incorporate these parameter fields right into your record selection formula.

In general, parameter fields provide interactivity for users, who respond to the parameter prompts in order to specify the data they want to see. However, by incorporating your parameter fields right into your record selection formula, you not only provide interactivity, but you also decrease data transfer and increase performance.

You can add a parameter field to your record selection formula by using the Select Expert or the Record Selection Formula Editor. When using the Record Selection Formula Editor, you treat the parameter field as you would any other field.

In the following example, you will create a parameter field and then add it to the record selection formula with the Select Expert. For further details on designing and creating parameter fields, see Parameter Fields.

To add a parameter field to your record selection formula
  1. Open the sample report group by intervals.rpt (located in the Feature Examples sample folder) in the Preview tab.

    Take a moment to navigate the Group tree and see how the data is organized; notice also, in the bottom right of the Crystal Reports window, that 270 records were returned for this report. Now click the Design tab.

  2. On the Insert menu, click Field Object.
  3. In the Field Explorer, right-click Parameter Fields and select New from the shortcut menu. The Create Parameter Field dialog box appears.
  4. For the Name of the parameter, type SalesQuota.
  5. For the Prompting text, type What was last year's sales quota?
  6. Click the Value type list and select Number.
  7. Make sure Discrete value(s) is selected, and then click OK.

    Note:    You've now created the parameter field. The rest of these procedures describe how to add the field to the record selection formula with the Select Expert.

  8. On the Report menu, click Select Expert. The Choose Field dialog box appears.
  9. Select the Customer.Last Year's Sales field, and then click OK. The Select Expert appears.
  10. On the Customer.Last Year's Sales tab, click the drop-down list and select is greater than. A new drop-down list will appear.
  11. Click this second list and select {?SalesQuota}.

    Tip:    Click the Show Formula button to view the new record selection formula, which appears as: {Customer.Last Year's Sales} > {?SalesQuota}. Instead of using the Select Expert, you could have created this formula yourself in the Record Selection Formula Editor. (To open the Editor, click Report > Edit Selection Formula > Record.)

  12. Click OK in the Select Expert.

You've now added your parameter field to the record selection formula. When you switch to Preview mode, or click the Refresh button, you will be prompted for new parameter values. You can then enter a numeric value representing last year's sales quota. The resulting report will display only those customers whose Last Year's Sales exceeds the numeric value you specify.

For instance, if you respond to the parameter by entering 40000, then the report will display only those customers whose sales exceeded $40,000 last year. Notice also, in the bottom right of the Crystal Reports window, that only 59 records are returned for your report, instead of the 270 records that were returned before you put the parameter field into a record selection formula.

By enhancing this report, you've retrieved all the information you needed and, at the same time, you've ensured that the fewest number of records is transferred from the database server.

Related topics

To create enhanced parameters, see Advanced parameter features.

For general information about parameter fields, see Parameter overview.

For general details regarding record selection formulas, see Record Selection.



Seagate Software IMG Holdings, Inc.
http://www.seagatesoftware.com
Support services:
http://support.seagatesoftware.com